
const Koa = require('koa');
const fs = require('fs');
const app = new Koa();
const request = require('koa/lib/request');
const formidable = require('koa-formidable')  //图片处理
const path = require('path')
const serve = require('koa-static')
var mysql = require('mysql');
const router = require("koa-router")();
const query = require("../mysql/query"); //引入异步查询方法
app.use(serve(__dirname))//设置静态文件
const nxlsx = require('node-xlsx')

//商品接口
var goods = async (ctx, next) => {
  let name = ctx.request.body.name || '';
  let merchant = ctx.request.body.merchant || '';
  let pageSize = ctx.request.body.pageSize;
  let pageNum = ctx.request.body.pageNum;
  let sqlAll = `SELECT COUNT(*) num FROM goods_enter WHERE name LIKE '%${name}%' AND merchant LIKE '%${merchant}%'`
  let sql = `SELECT * FROM goods_enter WHERE name LIKE '%${name}%' AND merchant LIKE '%${merchant}%' LIMIT ${pageSize} OFFSET ${pageSize*(pageNum-1)} `
  let query_res = await query(sql)
  let res = await query(sqlAll)
  ctx.body = {
    data:{
      dataList:query_res,
      all:res[0].num,
    },
    code:'200',
    msg:'成功'
  }
};

// 商品入库接口
var goodsEnter = async (ctx, next) => {
  let name = ctx.request.body.name || '';
  let goods_id = ctx.request.body.goods_id || '';
  let type = ctx.request.body.type || '';
  let count = ctx.request.body.count || 0;
  let price = ctx.request.body.price || 0;
  let unit = ctx.request.body.unit || '';
  let merchant = ctx.request.body.merchant || '';
  let isSigned = ctx.request.body.isSigned || '';
  let content = ctx.request.body.content || '';
  let used = ctx.request.body.used || 0;
  let last = ctx.request.body.last || 0;
  let settled = ctx.request.body.settled || 0;
  let goods_img = ctx.request.body.goods_img || '';
  var  addSql = 'INSERT INTO goods_enter(name,goods_id,type,count,price,unit,merchant,isSigned,content,used,last,settled,goods_img) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)';
  var  addSqlParams = [name,goods_id,type,count,price,unit,merchant,isSigned,content,used,last,settled,goods_img];
  await query(addSql,addSqlParams)
  ctx.body = {
    code:'200',
    msg:'成功'
  }
};

// 商品详情
var goodsDetail = async(ctx,next)=> {
  let id = ctx.request.body.id || '';
  var  addSql = `SELECT * FROM goods_enter WHERE id=${id}`;
  let res = await query(addSql)
  ctx.body = {
    data:res,
    code:'200',
    msg:'成功'
  }
}

// 商品结算
var goodsSettle = async(ctx,next)=> {
  let goodsId = ctx.request.body.goodsId || '';
  var  addSql = `SELECT * FROM goods_enter WHERE goods_id=${goodsId}`;
  let res = await query(addSql)
  ctx.body = {
    data:res,
    code:'200',
    msg:'成功'
  }
}

//商品申请
var goodsApply = async (ctx, next) => {
  let name = ctx.request.body.name || '';
  let goods_id = ctx.request.body.goods_id || '';
  let count = ctx.request.body.count || 0;
  let price = ctx.request.body.price || 0;
  let merchant = ctx.request.body.merchant || '';
  let applyer = ctx.request.body.applyer || '';
  let tips = ctx.request.body.tips || '';
  var  addSql = 'INSERT INTO apply_table(name,goods_id,count,price,merchant,applyer,tips) VALUES(?,?,?,?,?,?,?)';
  var  addSqlParams = [name,goods_id,count,price,merchant,applyer,tips];
  await query(addSql,addSqlParams)
  ctx.body = {
    code:'200',
    msg:'成功'
  }
};

//商品申请列表
var applyList = async (ctx, next) => {
  let sql = 'SELECT * FROM apply_table LIMIT 10 OFFSET 0'
  let res = await query(sql)
  ctx.body = {
    data:res,
    code:'200',
    msg:'成功'
  }
};

//申请详情
var applyDetail = async (ctx, next) => {
  let id = ctx.request.body.id || '';
  let sql = `SELECT * FROM apply_table WHERE id=${id}`
  let res = await query(sql)
  ctx.body = {
    data:res,
    code:'200',
    msg:'成功'
  }
};

//撤回申请
var recallApply = async (ctx, next) => {
  let id = ctx.request.body.id || '';
  let sql = `DELETE FROM apply_table WHERE id=${id}`
 await query(sql)
  ctx.body = {
    code:'200',
    msg:'撤回成功'
  }
};

function group(array, subGroupLength) {
  let index = 0;
  let newArray = [];
  while(index < array.length) {
  newArray.push(array.slice(index, index += subGroupLength));
  }
  return newArray;
  }
  

//表格下载
var excelDown = async (ctx, next) => {
  let sql = 'SELECT goods_id,name,count,price,merchant,isSigned FROM goods_enter'
  let res = await query(sql)
  let table_data = []
  let allData = []
  res.forEach(item => {
    for(var i in item){
      table_data.push(item[i])
    }
  });
  allData = group(table_data, 6);
  allData.unshift(['材料id','名称','总量','价格','商家','是否签订合同'])
  const table_header = allData;
  let buffer = nxlsx.build([{name:"test",data:table_header}]);
  ctx.body = buffer    
};


module.exports = {
  'POST /goodsList': goods,
  'POST /goodsEnter': goodsEnter,
  'POST /goodsDetail': goodsDetail,
  'POST /goodsSettle': goodsSettle,
  'POST /goodsApply': goodsApply,
  'POST /applyList': applyList,
  'POST /applyDetail': applyDetail,
  'POST /recallApply': recallApply,
  'POST /download': excelDown
};